﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MLVS.Model;
using MLVS.AppUtils;
using nsBaseClass;
using log4net;

namespace MLVS.Database
{
    public class DBAccess
    {
        public static readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public static void insertCVINS(List<CVINS> cvins)
        {
            log.Debug("Insert CVINS to DB starting....");

            //Constructing a sqlfactory class for sql usage 
            //string sql = "INSERT INTO CVINS ( ASKPR, COLOURCD, DEFVATCD, ESVATCD, EWA, IMPCODE, ISPUBLISHED, LICNO, LOCATION, MODEL, LISTPRICE, MAKE,MLVSDEALER,NOTE, SERIALNO,SMANID,SUBMODEL, TOTOEQS, TOTOEQS0,TRIMCD,TYPECODE,VISIBLE) VALUES {0} ";
            //string endSql = "";
            foreach (var c in cvins)
            {
                clsSqlFactory hSql = new clsSqlFactory(); //Constructing a sqlfactory class for sql usage 
                bool flag = false;
                int id1 = -1;
                id1 = GetMaxCVINSID();
                int CVINSID = 0;
                if (id1 == -1)
                    CVINSID = id1 + 2;
                else CVINSID = id1 + 1;
                c.VINSID = CVINSID;
                string sql = "INSERT INTO CVINS ( ASKPR, COLOURCD, DEFVATCD, ESVATCD, EWA, IMPCODE, ISPUBLISHED, LICNO, LOCATION, MODEL, LISTPRICE, MAKE,MLVSDEALER,NOTE, SERIALNO,SMANID,SUBMODEL, TOTOEQS, TOTOEQS0,TRIMCD,TYPECODE,VISIBLE, CVINSID,VINSID ,UNITID) VALUES {0} ";
                string endSql = "";
                //string temp = "({0},'{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}',{10},'{11}','{12}','{13}','{14}','{15}','{16}',{17},{18},'{19}','{20}',{21})";
                string temp = "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',{21},{22},{23},'{24}')";
                string[] pars = new string[] { c.ASKPR.ToString(), StringUtils.SubstringMax(c.COLOURCD, 10), StringUtils.SubstringMax(c.DEFVATCD, 1), 
                        StringUtils.SubstringMax(c.ESVATCD,1),StringUtils.SubstringMax(c.EWA,6),StringUtils.SubstringMax(c.IMPCODE,20),c.ISPUBLISHED.ToString(),
                        StringUtils.SubstringMax(c.LICNO,15),StringUtils.SubstringMax(c.LOCATION,10),StringUtils.SubstringMax(c.MODEL,80),c.LISTPRICE.ToString(),
                        StringUtils.SubstringMax(c.MAKE,50),StringUtils.SubstringMax(c.MLVSDEALER,10),c.NOTE,StringUtils.SubstringMax(c.SERIALNO,20),
                        StringUtils.SubstringMax(c.SMANID,5),StringUtils.SubstringMax(c.SUBMODEL,150),c.TOTOEQS.ToString(),c.TOTOEQS0.ToString(),
                        StringUtils.SubstringMax(c.TRIMCD,10),StringUtils.SubstringMax(c.TYPECODE,50),c.VISIBLE.ToString(), CVINSID.ToString(),CVINSID.ToString(), StringUtils.SubstringMax(c.UNITID, 10)
                      };
                temp = string.Format(temp, pars);
                endSql += temp;
                //temp = "({0},'{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}',{10},'{11}','{12}','{13}','{14}','{15}','{16}',{17},{18},'{19}','{20}',{21}),";
                sql = string.Format(sql, endSql);

                try
                {

                    hSql.NewCommand(sql);
                    if (hSql.Com.Transaction.Connection == null)
                        log.Error("Transaction is null");
                    int val = -1;
                    flag = hSql.ExecuteNonQuery(out val);
                    hSql.Commit();
                    log.Debug("Insert CVINS finished");

                }
                catch (Exception ex)
                {
                    try
                    {
                        flag = false;
                        hSql.Rollback();
                        log.Error("Insert CVINS error");
                        log.Error(ex.Message);
                    }
                    catch (Exception)
                    {

                        // Do nothing here; transaction is not active.
                    }
                    //throw ex;
                }
                finally
                {
                    hSql.Close();
                }

                // get id_inssert
                int id = -1;
                id = GetMaxCVINSID();

                if (id != -1 && flag)
                {
                    insertCVOEQ(c.cvoeqs, id.ToString());
                }

            }

        }




        public static void insertCVOEQ(List<CVOEQ> cvoeq, string CVINSID)
        {
            log.Debug("Insert CVOEQ to DB starting....");
            clsSqlFactory hSql = new clsSqlFactory(); //Constructing a sqlfactory class for sql usage 

            try
            {
                string sql = "INSERT INTO CVOEQ ( OEQCID, NAME,ACCCODE, ACCTYPE, CURRCD, INSDATE, SELPR, SUBID, VATPC, CVINSID ) VALUES {0} ";
                string endSql = "";
                foreach (var c in cvoeq)
                {
                    string temp = "('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}'),";
                    string[] pars = new string[] {  StringUtils.SubstringMax(c.OEQCID, 22), StringUtils.SubstringMax(c.Name, 100), 
                        StringUtils.SubstringMax(c.ACCCODE,40),StringUtils.SubstringMax(c.ACCTYPE,4),StringUtils.SubstringMax(c.CURRCD,5),
                        string.IsNullOrEmpty(c.INSDATE) ? "null" : string.Format("CONVERT (datetime, '{0}', 126)",c.INSDATE),
                        c.SELPR_STR,c.SUBID.ToString(),c.VATPR.ToString(), CVINSID
                      };
                    temp = string.Format(temp, pars);
                    endSql += temp;
                    temp = "('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},{8},{9}),";

                }
                endSql = endSql.Substring(0, endSql.Length - 1);
                sql = string.Format(sql, endSql);

                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();

                // log.Debug("Insert CVOEQ Rows Affected: " + val);


                log.Debug("Insert CVOEQ finished");
            }
            catch (Exception ex)
            {
                hSql.Rollback();
                log.Error("Insert CVOEQ error");
                log.Error(ex.Message);
                //throw ex;
            }
            finally
            {
                hSql.Close();
            }
        }
        public static void ClearCVOEQ()
        {
            log.Debug("Clear table CVOEQ....");
            clsSqlFactory hSql = new clsSqlFactory();
            string sql = "DELETE FROM CVOEQ ";
            try
            {
                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();
                log.Debug("Delete from CVOEQ :" + val + " rows.");
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                hSql.Rollback();
            }
            finally
            {
                hSql.Close();
            }
        }


        public static void ClearCVINS()
        {
            log.Debug("Clear table CVINS....");
            clsSqlFactory hSql = new clsSqlFactory();
            // string sql = "DELETE FROM CVOEQ ";
            string sql2 = "DELETE FROM CVINS ";
            try
            {
                //hSql.NewCommand(sql);
                //
                //hSql.ExecuteNonQuery(out val);
                //hSql.Commit();
                //log.Debug("Delete from CVOEQ :" + val + " rows.");
                int val = -1;
                hSql.NewCommand(sql2);
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();
                log.Debug("Delete from CVINS :" + val + " rows.");

            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                hSql.Rollback();
                // throw ex;
            }
            finally
            {
                hSql.Close();
            }
        }

        public static void TruncateCVOEQ()
        {
            log.Debug("Truncate table CVOEQ starting....");
            clsSqlFactory hSql = new clsSqlFactory();
            string sql = "TRUNCATE TABLE CVOEQ ";
            try
            {
                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();
                log.Debug("Truncate CVOEQ successful.");
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                hSql.Rollback();
            }
            finally
            {
                hSql.Close();
            }
        }

        public static void TruncateCVINS()
        {
            log.Debug("Truncate table CVINS starting....");
            clsSqlFactory hSql = new clsSqlFactory();
            string sql = "TRUNCATE TABLE CVINS ";
            try
            {
                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();
                log.Debug("Truncate CVINS successful.");
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                hSql.Rollback();
            }
            finally
            {
                hSql.Close();
            }
        }


        public static int GetIdInsert(string table)
        {
            try
            {
                string sql = "SELECT IDENT_CURRENT('{0}')";
                sql = string.Format(sql, table);
                clsSqlFactory hSql = new clsSqlFactory();
                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteReader();
                if (hSql.Read())
                {
                    val = hSql.Reader.GetInt32(0);    //Read from readerstream to array of strings
                }
                hSql.Close();
                log.Debug("Max_ID of table " + table + " : " + val.ToString());
                return val;
            }
            catch (Exception ex)
            {
                string error = "Can't get max id of {0} : {1}";
                log.Error(string.Format(error, table, ex.Message));
                return -1;
                // throw ex;
            }
        }


        public static int GetMaxCVINSID()
        {
            try
            {
                string sql = "select MAX(CVINSID) FROM CVINS;";
                clsSqlFactory hSql = new clsSqlFactory();
                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteReader();
                if (hSql.Read())
                {
                    val = hSql.Reader.GetValue(0) == DBNull.Value ? -1 : hSql.Reader.GetInt32(0);    //Read from readerstream to array of strings
                }
                hSql.Close();
                log.Debug("Max_ID of table CVINS : " + val.ToString());
                return val;
            }
            catch (Exception ex)
            {
                string error = "Can't get max id of {0} : {1}";
                log.Error(string.Format(error, "CVINS", ex.Message));
                return -1;
                // throw ex;
            }
        }

    }
}
